﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;

namespace Sunyi.Project.WebPoint.DataAccess
{
    public static class DaoHelper
    {
        public static void FillEntity<T>(ICollection<T> collection, Guid entityId, string columnName, string tableName, string valueColumnName) //where T : class
        {
            const string SqlFormat = "SELECT {0} FROM {1} WHERE {2} = @{2} AND IsActive = 1";

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format(SqlFormat, valueColumnName, tableName, columnName);
            command.Parameters.Add(new SqlParameter("@" + columnName, entityId));

            collection.Clear();

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    collection.Add((T)reader[valueColumnName]);
                }
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public static void FillEntity<Key, Value>(IDictionary<Key, Collection<Value>> dictionary, Guid entityId, string columnName, string tableName, string keyColumnName, string valueColumnName)
        {
            const string SqlFormat = "SELECT {0}, {1} FROM {2} WHERE {3} = @{3} AND IsActive = 1";

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format(SqlFormat, keyColumnName, valueColumnName, tableName, columnName);
            command.Parameters.Add(new SqlParameter("@" + columnName, entityId));

            dictionary.Clear();

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Key key = (Key)reader[keyColumnName];
                    if (!dictionary.ContainsKey(key))
                    {
                        dictionary.Add(key, Activator.CreateInstance<Collection<Value>>());
                    }

                    dictionary[key].Add((Value)reader[valueColumnName]);
                }
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public static void RemoveEntity(Guid entityId, string columnName, string tableName)
        {
            const string SqlFormat = "DELETE FROM {0} WHERE {1} = @{1}";

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format(SqlFormat, tableName, columnName);
            command.Parameters.Add(new SqlParameter("@" + columnName, entityId));

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public static void RemoveEntityCollection<T>(ICollection<T> collection, Guid entityId, string columnName, string tableName, string keyColumnName)
        {
            const string SqlFormat = "DELETE FROM {0} WHERE {1} = @{1} AND {2} = @{2}";

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format(SqlFormat, tableName, columnName, keyColumnName);

            try
            {
                connection.Open();

                foreach (T value in collection)
                {
                    command.Parameters.Clear();

                    command.Parameters.Add(new SqlParameter("@" + columnName, entityId));
                    command.Parameters.Add(new SqlParameter("@" + keyColumnName, value));

                    command.ExecuteNonQuery();
                }
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public static void InsertEntity<T>(ICollection<T> collection, Guid entityId, string columnName, string tableName, string valueColumnName)
        {
            const string SqlFormat = "INSERT INTO {0}(Id, {1}, {2}, IsActive) VALUES(NEWID(), @{1}, @{2}, 1)";

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format(SqlFormat, tableName, columnName, valueColumnName);

            try
            {
                connection.Open();

                foreach (T value in collection)
                {
                    command.Parameters.Clear();

                    command.Parameters.Add(new SqlParameter("@" + columnName, entityId));
                    command.Parameters.Add(new SqlParameter("@" + valueColumnName, value));

                    command.ExecuteNonQuery();
                }
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public static void InsertEntity<Key, Value>(IDictionary<Key, Collection<Value>> dictionary, Guid entityId, string columnName, string tableName, string keyColumnName, string valueColumnName)
        {
            const string SqlFormat = "INSERT INTO {0}({1}, {2}, {3}, IsActive) VALUES(@{1}, @{2}, @{3}, 1)";

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format(SqlFormat, tableName, columnName, keyColumnName, valueColumnName);

            try
            {
                connection.Open();

                foreach (Key key in dictionary.Keys)
                {
                    foreach (Value value in dictionary[key])
                    {
                        command.Parameters.Clear();

                        command.Parameters.Add(new SqlParameter("@" + columnName, entityId));
                        command.Parameters.Add(new SqlParameter("@" + keyColumnName, key));
                        command.Parameters.Add(new SqlParameter("@" + valueColumnName, value));

                        command.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }
    }
}
